/*
This file contains adhoc queries, and some old queries that aren't organized.

*/

--For SQL Developer, disable variable substitution.  For TOAD, right-click in SQL Editor and uncheck "Prompt for
--Substitution Variables"
SET SCAN OFF;
set define off;



------------------------------------------------------------------------------------------------------------------------
--001 - GDP Equivalant amount of money (how much do we give, how much do they get)
------------------------------------------------------------------------------------------------------------------------

--Create bar chart of Amount Give and Amount Received, based on per-capita GDP PPP.

--SEE v_loansize_purchasing_power for version used in website


select '<img border="0" src="http://chart.apis.google.com/chart?cht=bhg'||
  '&chtt=Total+loan+amount+in+local+purchasing+power&chd=t:'||givenPercent||','||receivedPercent||'&chs=450x100'||
  '&chco=FF9900,000000&chxt=y&chxl=0:|Amount+received:+$'||received||'|Amount+given:+$'||given||'|"/>' image
from
(
  select trim(to_char(totalLoanAmount.totalLoanAmount / 1000000, '999'))||' million' given
    ,trim(to_char(totalLoanAmount * moneyMultiplier / 1000000000, '9.99'))||' billion' received
    ,trim(to_char(totalLoanAmount.totalLoanAmount / 
      (totalLoanAmount * moneyMultiplier + totalLoanAmount) * 100, '99')) givenPercent
    ,trim(to_char(totalLoanAmount * moneyMultiplier / 
      (totalLoanAmount * moneyMultiplier + totalLoanAmount) * 100, '99')) receivedPercent
  from
  (
    --Find the average money multiplier
    select avg(lenderCountry.gdpPerCapitaPPP / loanCountry.gdpPerCapitaPPP) moneyMultiplier
    from loan_lender
      inner join loan on loan_lender.loan_id = loan.id
      inner join country loanCountry on loan.country = loanCountry .name
      inner join lender on loan_lender.lender_id = lender.lender_id
      inner join country lenderCountry on lender.country_code = lenderCountry.iso_code
    where loan.status not in ('refunded', 'fundraising')
  ) averageMoneyMultiplier
  cross join
  (
  --Total amount of money loaned
    select sum(loan_amount) totalLoanAmount
    from loan
    where loan.status not in ('refunded', 'fundraising')
  ) totalLoanAmount
);



--How many lending actions per country?
select country, total, ratio_to_report(total) over ()
from
(
  select country.name country, count(loan_lender.lender_id) total
  from loan_lender
    inner join lender on loan_lender.lender_id = lender.lender_id
    inner join country on lender.country_code = country.iso_code
  group by country.name
  order by total desc
);


--This one guy single-handedly makes Christmas Islands one of the top lending countries.
select * from lender where country_code = 'CX';


--What's the least and most efficient loan?  (flip order by from ascending to descending)
select loan.id, loanCountry.name loanCountry, loanCountry.gdpPerCapitaPPP loan_gdpPerCapitaPPP
  ,lender.lender_id, lenderCountry.name lenderCountry, lenderCountry.gdpPerCapitaPPP lender_gdpPerCapitaPPP
  ,lenderCountry.gdpPerCapitaPPP / loanCountry.gdpPerCapitaPPP moneyMultiplier
from loan_lender
  inner join loan on loan_lender.loan_id = loan.id
  inner join country loanCountry on loan.country = loanCountry .name
  inner join lender on loan_lender.lender_id = lender.lender_id
  inner join country lenderCountry on lender.country_code = lenderCountry.iso_code
order by moneyMultiplier desc;


--Percent of lenders per country
select country_code, total, ratio_to_report(total) over ()
from
(
  select country_code, sum(loan_count) total
  from lender
  where loan_count is not null
    and country_code is not null
  group by country_code
)
order by total desc;









-------------------------------------------------------------------------------
--002 - How does gender affect fundraising in poorer and richer nations?
-------------------------------------------------------------------------------
--Hypothosis: Loans will be funded faster for women the poorer their country is, and loans will be funded
--faster for men the richer their country is.








select
  to_string2(cast(collect
    ('['||trim(to_char(gdpPerCapitaPPP))||','||trim(to_char(percentTimeToLoan, '0.00'))||']'
    order by gdpPerCapitaPPP) as varchar2_ntt), ', ')
from
(
  select country, gdpPerCapitaPPP, gender, avg(percentTimeToLoan) percentTimeToLoan
  from
  (
    select distinct loan_id, minutesToFundDollar / loansAvailAvgMinToFundDollar percentTimeToLoan
      ,country.name country, country.gdpPerCapitaPPP
      ,case when maleOnly = 1 then 'Male' when femaleOnly = 1 then 'Female' else 'Neither' end gender
    from loansAvailableWhenposted
      inner join loan on loansAvailableWhenPosted.loan_id = loan.id
      inner join country on loan.country = country.name
      --Only count loans as male or female if they are completely male or completely female
      inner join
      (
        select loan_id
          --If there is a single female, it's not male only.  Vice-versa for female only.
          ,min(case when gender = 'F' then 0 else 1 end) maleOnly
          ,min(case when gender = 'M' then 0 else 1 end) femaleOnly
        from borrower
        group by loan_id
      ) loan_gender
        on loan.id = loan_gender.loan_id
      --Only include countries with GDP information
    where country.gdpPerCapitaPPP is not null
    order by loan_id
  )
  group by country, gdpPerCapitaPPP, gender
)
;

select loan_id
  --If there is a single female, it's not male only.  Vice-versa for female only.
  ,min(case when gender = 'F' then 0 else 1 end) maleOnly
  ,min(case when gender = 'M' then 0 else 1 end) femaleOnly
from borrower
group by loan_id;


      Male only, female only
131217	1	0
128911	0	0
120718	0	1
120802	0	1

select * from borrower where loan_id in (131217, 128911, 120718, 120802) order by loan_id






--Plot of male percent of time to loan.  X axis is GDP, Y axis is percent.
select
  to_string2(cast(collect
    ('['||trim(to_char(gdpPerCapitaPPP))||','||trim(to_char(percentTimeToLoan, '0.00'))||']'
    order by gdpPerCapitaPPP) as varchar2_ntt), ', ')
from
(
  select country, gdpPerCapitaPPP, avg(percentTimeToLoan) percentTimeToLoan
  from
  (
    select distinct loan_id, minutesToFundDollar / loansAvailAvgMinToFundDollar percentTimeToLoan
      ,country.name country, country.gdpPerCapitaPPP
    from loansAvailableWhenposted
      inner join loan on loansAvailableWhenPosted.loan_id = loan.id
      inner join country on loan.country = country.name
      inner join borrower on loan.id = borrower.loan_id
      --Only include men (there may be multiple per loan, must use a distinct in the select)
    where borrower.gender = 'M'
      --Only include countries with GDP information
      and country.gdpPerCapitaPPP is not null
    order by loan_id
  )
  group by country, gdpPerCapitaPPP
)
;

--Plot of female percent of time to loan.  X axis is GDP, Y axis is percent.
select
  to_string2(cast(collect
    ('['||trim(to_char(gdpPerCapitaPPP))||','||trim(to_char(percentTimeToLoan, '0.00'))||']'
    order by gdpPerCapitaPPP) as varchar2_ntt), ', ')
from
(
  select country, gdpPerCapitaPPP, avg(percentTimeToLoan) percentTimeToLoan
  from
  (
    select distinct loan_id, minutesToFundDollar / loansAvailAvgMinToFundDollar percentTimeToLoan
      ,country.name country, country.gdpPerCapitaPPP
    from loansAvailableWhenposted
      inner join loan on loansAvailableWhenPosted.loan_id = loan.id
      inner join country on loan.country = country.name
      inner join borrower on loan.id = borrower.loan_id
      --Only include men (there may be multiple per loan, must use a distinct in the select)
    where borrower.gender = 'F'
      --Only include countries with GDP information
      and country.gdpPerCapitaPPP is not null
    order by loan_id
  )
  group by country, gdpPerCapitaPPP
);























--Men only
select country, gdpPerCapitaPPP, avg(percentError) percentError, count(loan_id) numberOfLoans
from
(
  select distinct loan.id loan_id, country.name country, country.gdpPerCapitaPPP
    ,(minutesToFundDollar - loansAvailAvgMinToFundDollar) / loansAvailAvgMinToFundDollar * 100 percentError
  from loansAvailableWhenPosted, loan, country, borrower
  where loansAvailableWhenPosted.loan_id = loan.id
    and loan.country = country.name
    and loan.id = borrower.loan_id
    and borrower.gender = 'M'
)
group by country, gdpPerCapitaPPP;


--Women only
select country, gdpPerCapitaPPP, avg(percentError) percentError, count(loan_id) numberOfLoans
from
(
  select distinct loan.id loan_id, country.name country, country.gdpPerCapitaPPP
    ,(minutesToFundDollar - loansAvailAvgMinToFundDollar) / loansAvailAvgMinToFundDollar * 100 percentError
  from loansAvailableWhenPosted, loan, country, borrower
  where loansAvailableWhenPosted.loan_id = loan.id
    and loan.country = country.name
    and loan.id = borrower.loan_id
    and borrower.gender = 'F'
)
group by country, gdpPerCapitaPPP;




--Women only
select country.name, country.gdpPerCapitaPPP
  ,avg((minutesToFundDollar - loansAvailAvgMinToFundDollar) / loansAvailAvgMinToFundDollar * 100) percentError
  ,count(loan.id) numberOfLoans
from loansAvailableWhenPosted, loan, country
where loansAvailableWhenPosted.loan_id = loan.id
  and loan.country = country.name
  and loan.id not in (select loan_id from borrower where gender = 'M')
group by country.name, country.gdpPerCapitaPPP





select * from country

select * from loan;


select loan_id, minutesToFundDollar, loansAvailAvgMinToFundDollar
from loansAvailableWhenPosted
where minutesToFundDollar > loansAvailAvgMinToFundDollar;

select loan_id from borrower where gender = 'M';







































------------------------------------------------------------------------------------------------------------------------
--003 - Mike LeGower
------------------------------------------------------------------------------------------------------------------------

select * from loan where status not in ('in_repayment', 'paid', 'funded', 'fundraising') order by id;

select * from payment where loan_id = 392;
select * from localPayment where loan_id = 392;
select * from scheduledPayment where loan_id = 392;

select * from payment where loan_id = 78849;
select * from localPayment where loan_id = 78849;
select * from scheduledPayment where loan_id = 78849;

scheduledPayment is table on website;

select * from payment where loan_id = 392;


select loan_id, amount, processed_date
  ,sum(amount) over (order by processed_date rows between unbounded preceding and current row) runningTotal
from payment
where loan_id = 392
order by processed_date;

select loan_id, due_date, amount
  ,sum(amount) over (order by due_date rows between unbounded preceding and current row) runningTotal
from localPayment
where loan_id = 392
order by due_date;



/*

*/


select * from payment;

select * from localPayment;

select * from scheduledPayment;


select count(*) from loan;

select count(*) from loan where status = 'defaulted';


select *
from lender
where upper(name) like '%WENDY%'
  and country_code = 'CA'
  and loan_count = 1
  and upper(whereabouts) like '%ONTARIO%';

--wendy8402

select * from loan_lender where lender_id = 'wendy8402';



















------------------------------------------------------------------------------------------------------------------------
--004 - Alison Carlman 
------------------------------------------------------------------------------------------------------------------------

--Kiva statistics page: http://www.kiva.org/about/facts/#FactSheet

--Total amount loaned, including loans still in fundraising.
--93,752,460

--Kiva #: $93,356,760

select sum(loan_amount) from loan;

--Number of lenders
--491118 total lenders, but only 318137 (64.8%) of them have made a loan.

--Kiva #: 563,271 (I guess this includes anonymous users?)

select totalUsers, usersWithALoan
  ,trim(to_char(usersWithALoan/totalUsers*100, '990.0'))||'%' percentOfUsersWithALoan
from
(
  select count(lender.lender_id) totalUsers
    ,count(case when loan_count > 0 then lender.lender_id else null end) usersWithALoan
  from lender
);

--Number of borrowers
--230546
--Kiva #: 230,457
select count(*) from borrower;














------------------------------------------------------------------------------------------------------------------------
--004 - Christmas Island
------------------------------------------------------------------------------------------------------------------------

--How many lending actions per country?
select country, total "# Loans", trim(round(ratio_to_report(total) over () * 100, 1)) "%"
from
(
  select country.name country, count(loan_lender.lender_id) total
  from loan_lender
    inner join lender on loan_lender.lender_id = lender.lender_id
    inner join country on lender.country_code = country.iso_code
  group by country.name
  order by total desc
);


--This one guy single-handedly makes Christmas Islands one of the top lending countries.
select * from lender where country_code = 'CX';
select * from lender where country_code = 'AQ';

--Average amount lent per lending action - 37.8
select 
  (select sum(loan_amount) from loan where status not in ('refunded', 'fundraising')) /
  (select count(*) from loan_lender)
from dual;

select (select 1 from dual) / (select 2 from dual) from dual;




/*
#24 - Christmas Island?

Below are the top ten countries, ranked by the number of loans per country.  The amount of money each individual lends
is not available, so I can only hoope that the number of lending actions correlates with the amount given.  (This
could be false if people from some countries tend to make fewer but larger loans, or if people in some countries are more
likely to lend anonymously.)

United States	  1,603,062	70.1%
Canada	  200,554	8.8%
United Kingdom	  78,735	3.4%
Australia	  66,319	2.9%
Germany	  44,815	2.0%
Netherlands	  39,575	1.7%
France	  29,479	1.3%
Norway	  28,039	1.1%
Sweden	  21,905	1.0%
Belgium	  21,756	1.0%

The list is kind of what you would expect for an organization based in America, and mostly in English.  But there are
a few surprises.  Since the lender's country is self-reported, there is certainly some bad data.  For example, Antarctica
is #91, with 62 loans from two people.  The oddest result is Christmas Island, #24 with 3,408 loans, between
Poland and United Arab Emirates.  However, all of these loans are from one person, and based on his lender page I think
it's safe to say he's not from Christmas Island.  But it's interesting how much affect a single person can have on
the results.  With a minimum of $25 per loan, this is over $85,000 from a single lender!  (More of top lenders later...)

*/




------------------------------------------------------------------------------------------------------------------------
--005 - Repayment term
------------------------------------------------------------------------------------------------------------------------
/*Loan ID Query Website
  1252    13.9  14
  1256    19.8  20
  1373    7.86  8
  1611    15.7  16
  2620    14.3  15
  2748    5.37  6
  3039    5.17  6
  3805    11.50 12
  3806    12.46 13
  3884    12.24 13
  4127    5.15  6
  45887   11.58 12
  64926   12.16 13
  82312   7.45  8
  98687   6     6
  98688   13.8  14
  115311  13.19 14
*/


--Average loan term (repayment term): the number of months between the disbursal_date and the last scheduled date.
--This is the loan schedule from the lender's point of view.  You can change the table scheduledPayment to localPayment
--to get the schedule from the borrower's perspective.
select avg(repaymentMonths) averageRepaymentMonths
  ,sum(repaymentMonths * loan_amount) / sum(loan_amount) weightedAvgRepaymentMonths
from
(
  select loan_id, loan_amount, disbursal_date, lastPayment
    --There is no universally accepted method of calculating the number of months between dates.  months_between()
    --tries to return a natural number of months when possible.  For example, the difference between 'Jan 28' and
    --'Feb 28' is 1, and 'Jan 31' and 'Feb 28' is also 1, but 'Jan 29' and 'Feb 28' is .97.  I'm not sure how this
    --compares to the method Kiva uses.
    --The Repayment Term displayed on each loan page appears to always round up fractional results, but this query
    --does not do that.  Add the function ceil() around the months_between() to always round up.
    ,months_between(lastPayment, disbursal_date) repaymentMonths
  from
    loan
    ,(select loan_id, max(due_date) lastPayment
      from scheduledPayment
      group by loan_id
      order by loan_id
    ) schedule
  where loan.id = schedule.loan_id
  order by loan.id
)
;








Depending on how you look at it I think the over-all trend is that repayment terms are decreasing.  Here is a plot of the average repayment term per month:
http://chart.apis.google.com/chart?cht=lc&chs=650x450&chtt=Repayment+term+per+month&chds=0,15&chxt=x,y&chxl=0:|2006%20May|2009%20Aug|1:|0|15&chd=t:11.9,12.6,12.8,11.6,11.9,10.0,9.5,10.9,11.5,11.9,12.3,11.7,12.2,12.1,12.9,12.0,12.6,12.3,12.0,11.8,12.4,11.9,11.9,11.5,11.3,11.5,11.6,11.6,12.1,11.4,11.0,11.1,11.2,11.0,10.6,10.3,10.2,10.7,12.1,13.9

The average was lower near the beginning, and went up significantly recently, but both the early months and the recent months have a smaller number of values.  I think the most significant trend may be the gradual decrease in the middle of the chart.

Below is the query used to generate the plot.  As with the previous query, this is the number of months between the disbursal date and the last scheduled payment (so it's from the lender's perspective); the number of months is not rounded up (the Kiva loan page seems to round up); and the months_between() function compares the day of the month instead of simply dividing by a constant such as 30 days.  (Does someone know the exact formula used by Kiva?)

--Repayment months per month 
select to_char(disbursal_date, 'YYYYMM') disbursalDate 
  ,trim(to_char(avg(repaymentMonths), '90.0')) averageRepaymentMonths 
  ,trim(to_char(sum(repaymentMonths * loan_amount) / sum(loan_amount), '90.0')) weightedAvgRepaymentMonths 
from 
( 
  select loan_id, loan_amount, disbursal_date, lastPayment 
    ,months_between(lastPayment, disbursal_date) repaymentMonths 
  from loan 
    ,(select loan_id, max(due_date) lastPayment from scheduledPayment group by loan_id) schedule 
  where loan.id = schedule.loan_id 
) 
group by to_char(disbursal_date, 'YYYYMM') 
order by disbursalDate 


Here is the repayment months per country query:

select country 
  ,trim(to_char(avg(repaymentMonths), '90.0')) averageRepaymentMonths 
  ,trim(to_char(sum(repaymentMonths * loan_amount) / sum(loan_amount), '90.0')) weightedAvgRepaymentMonths 
from 
( 
  select country, loan_id, loan_amount, disbursal_date, lastPayment 
    ,months_between(lastPayment, disbursal_date) repaymentMonths 
  from loan 
    ,(select loan_id, max(due_date) lastPayment from scheduledPayment group by loan_id) schedule 
  where loan.id = schedule.loan_id 
) 
group by country 
order by country 












--Loans without a scheduled payment.
--The first 205 loans don't have a scheduled payment.  Also, loan 82765 is missing scheduled payments.
select * from loan where id in
(
  select id from loan
  minus
  select distinct loan_id from scheduledpayment
)
order by loan.id;


















-------------------------------------------------------------------------------
--006 - Funded Date
-------------------------------------------------------------------------------


--Look for the gaps between when any loans were available.
--This happens whenever the posted_date is later than the latest previous funded_date.
--select loan_id, posted_date, funded_date
select loan_id, posted_date, funded_date, gap
from
(
  select loan_id, posted_date, funded_date
    ,case when posted_date > latestPreviousFundedDate then posted_date - latestPreviousFundedDate else null end gap
  from
  (
    select loan.id loan_id, posted_date, funded_date
      ,max(funded_date) over (order by posted_date rows between unbounded preceding and 1 preceding)
        latestPreviousFundedDate
    from loan
    where status not in ('fundraising', 'refunded') and funded_date >= posted_date
    order by loan.posted_date
  )
)
where gap is not null
order by gap desc
;

--The above numbers don't seem right to me.  I've seen several times where there were no loans available, so I would
--expect to see much more gaps.  Let's query the data a different way to double check.
--For all hours between the beginning and now, how many of them do not have any loans available during them?











--Average time to fund all loans
--2582.57 minutes
select avg(((funded_date - posted_date)*24*60)) minutesToFund
from loan
where status not in ('refunded', 'fundraising')
  and funded_date >= posted_date;

--Average time to fund loans per dollar of loan
--Funding $1 takes 3.5 minutes
select avg( (funded_date - posted_date) * 24 * 60 / loan_amount) minutesToFundPerDollar
from loan
where status not in ('refunded', 'fundraising')
  and funded_date >= posted_date;



create index idx_loan_funded_date on loan(funded_date);
create index idx_loan_posted_date on loan(posted_date);


--Find the time to fund all loans that were between posted_date and funded_date when the loan was posted.

--1,000 = 1 second
--5,000 = 12 seconds
--10,000 = 44 seconds


--Loans available when loan was posted
with loans as
(
  select * from
  (
    select loan.id, loan.posted_date, loan.funded_date, loan.loan_amount, country.region
      ,row_number() over (order by loan.id desc) rownumber
    from loan, country
    where loan.country = country.name
      and loan.status not in ('refunded', 'fundraising')
      and loan.funded_date >= loan.posted_date
  )
  --TEST: Use small number of values
  where rownumber <= 1000
)
------------------------------------------------------------------------------------------------------------------------
--select loanData.*
  --,trim(to_char(minutesToFundDollar - avgMinutesToFundDollar, '990.00')) difference
  --,trim(to_char(avg(minutesToFundDollar - avgMinutesToFundDollar)over(), '990.00')) averageDifference
  --region, trim(to_char(avg(minutesToFundDollar - avgMinutesToFundDollar), '990.00')) difference
--from
--(
  select loan1.id loan_id, loan1.region
    ,to_char(loan1.posted_date, 'DD Mon YYYY HH24:MI:SS') posted_date
    ,to_char(loan1.funded_date, 'DD Mon YYYY HH24:MI:SS') funded_date
    ,(loan1.funded_date - loan1.posted_date) * 24 * 60 minutesToFund
    ,(loan1.funded_date - loan1.posted_date) / loan1.loan_amount * 24 * 60 minutesToFundDollar
    ,avg(loan2.funded_date - loan2.posted_date) * 24 * 60 loansAvailAvgMinToFund
    ,avg((loan2.funded_date - loan2.posted_date) / loan2.loan_amount)*24*60 loansAvailAvgMinToFundDollar
    ,count(loan2.id) loansAvailableCount
    ,to_string(cast(collect(collect_obj(loan2.id, loan2.id)) as collect_ntt)) loansAvailableList
  from
    loans loan1
    left outer join loans loan2
      on loan1.id <> loan2.id and loan1.posted_date between loan2.posted_date and loan2.funded_date
  group by loan1.id, loan1.region, loan1.posted_date, loan1.funded_date, loan1.loan_amount
  order by loan1.id --loan1.funded_date desc --loan1.posted_date
--) loanData
--group by region order by difference desc
;









exec dbms_stats.gather_table_stats('KIVA','LOAN');



select * from user_tables;



--Must speed this up:

select loan1.id loan1_id, loan2.id loan2_id, loan1.posted_date, loan2.funded_date
from
  loan loan1
  left outer join loan loan2
    --on loan1.id <> loan2.id and loan1.posted_date between loan2.posted_date and loan2.funded_date
    --on loan1.id <> loan2.id
    on loan1.posted_date <= loan2.funded_date
;




--Loans available when loan was posted
with loans as
(
  select * from
  (
    select loan.id, loan.posted_date, loan.funded_date, loan.loan_amount, country.region
      ,row_number() over (order by loan.id desc) rownumber
    from loan, country
    where loan.country = country.name
      and loan.status not in ('refunded', 'fundraising')
      and loan.funded_date >= loan.posted_date
  )
  --TEST: Use small number of values
  where rownumber <= 1000
)
------------------------------------------------------------------------------------------------------------------------
--select loanData.*
  --,trim(to_char(minutesToFundDollar - avgMinutesToFundDollar, '990.00')) difference
  --,trim(to_char(avg(minutesToFundDollar - avgMinutesToFundDollar)over(), '990.00')) averageDifference
  --region, trim(to_char(avg(minutesToFundDollar - avgMinutesToFundDollar), '990.00')) difference
--from
--(
  select loan1.id loan_id, loan1.region
    ,to_char(loan1.posted_date, 'DD Mon YYYY HH24:MI:SS') posted_date
    ,to_char(loan1.funded_date, 'DD Mon YYYY HH24:MI:SS') funded_date
    ,(loan1.funded_date - loan1.posted_date) * 24 * 60 minutesToFund
    ,(loan1.funded_date - loan1.posted_date) / loan1.loan_amount * 24 * 60 minutesToFundDollar
    ,avg(loan2.funded_date - loan2.posted_date) * 24 * 60 loansAvailAvgMinToFund
    ,avg((loan2.funded_date - loan2.posted_date) / loan2.loan_amount)*24*60 loansAvailAvgMinToFundDollar
    ,count(loan2.id) loansAvailableCount
    ,to_string(cast(collect(collect_obj(loan2.id, loan2.id)) as collect_ntt)) loansAvailableList
  from
    loans loan1
    left outer join loans loan2
      on loan1.id <> loan2.id and loan1.posted_date between loan2.posted_date and loan2.funded_date
  group by loan1.id, loan1.region, loan1.posted_date, loan1.funded_date, loan1.loan_amount
  order by loan1.id --loan1.funded_date desc --loan1.posted_date
--) loanData
--group by region order by difference desc







Middle East	-1.66
Africa	-0.40
Eastern Europe	-0.37
South America	-0.36
Central America	-0.24
North America	-0.16
Asia	-0.12








--Time between posted and funded
select country, count(*) loanCount
  ,trim(to_char(min(timeToFund), '990.0')) minTime
  ,trim(to_char(max(timeToFund), '990.0')) maxTime
  ,trim(to_char(avg(timeToFund), '990.0')) avgTime
from
(
  select country, funded_date - posted_date timeToFund
  from loan
  where status not in ('refunded', 'fundraising')
    --Some loans are funded before they are posted, exclude them
    --so they won't throw off results.
    and funded_date - posted_date >= 0
)
group by country
order by avg(timeToFund);

Results (looks better in fixed-width font):

Country     loanCount minTime Maxtime avgTime
Nepal	      348	      0.0	    0.8	    0.1
Indonesia	  1678	    0.0	    2.4   	0.1
Palestine	  1	        0.2	    0.2	    0.2
Costa Rica	10	      0.1	    0.5   	0.3
Iraq	      164	      0.0	    1.2	    0.3
...



































------------------------------------------------------------------------------------------------------------------------
--007 - Lily Ho
------------------------------------------------------------------------------------------------------------------------
/*
Hi there,

I was wondering how many loans, and the amount of loans have been made to the United States so far. I realize your data isn't current, but let me know if you can help.

Thanks so much.

Best,
Lily Ho
*/

/*Lily,

101 loans/entrepreneurs and $525,725

Although my data is a few weeks old I was able to pull those numbers directly from Kiva's site, which should be current.
Two US partners are listed on this page, http://www.kiva.org/about/partners/   The amount loaned and number of entreprenuers/loans is listed on each page.

Let me know if you need any other data,

Jon

*/

--100, 540,725
select count(*) total, sum(loan_amount)
from loan
where country = 'United States'
;

From: http://www.kiva.org/about/partners/

58
$267,800

43
$257,925 

--The number of entreprenuers is 1 larger, but the dollar amount is 15K lower.  How'd that happen?
101
525725


------------------------------------------------------------------------------------------------------------------------
--008 - Cyrus Farivar (Wired Italy)
------------------------------------------------------------------------------------------------------------------------

--post these queries, send link to Cyrus


--Number of loans per country
--Italy is #14, with 14,300 
select country, numberOfLoans
  ,trim(to_char(ratio_to_report(numberOfLoans) over () * 100, '90.00'))||'%' percentOfAllLoans
  --,sum(numberOfLoans) over () totalLoans
  ,rank() over (order by numberOfLoans desc nulls last) loanRank
  ,numberOfLenders
  ,trim(to_char(ratio_to_report(numberOfLenders) over () * 100, '90.00'))||'%' percentOfAllLenders
  --,sum(numberOfLenders) over () totalLenders
  ,rank() over (order by numberOfLenders desc nulls last) lenderRank
from
(
  select country.name country, nvl(sum(loan_count), 0) numberOfLoans, count(lender.lender_id) numberOfLenders
  from lender
    left outer join country on lender.country_code = country.iso_code
  group by country.name
)
order by loanRank, lenderRank;


--Top lenders per country
select country, loan_count, lenderName, lender_id, loanRank
  ,trim(to_char(percentOfCountryLoans*100, '990.0'))||'%' percentOfCountryLoans
from
(
  select country.name country, lender.loan_count, lender.name lenderName, lender.lender_id
    ,rank() over (partition by country.name order by loan_count desc nulls last) loanRank
    ,ratio_to_report(lender.loan_count) over (partition by country.name) percentOfCountryLoans
  from lender
    left outer join country on lender.country_code = country.iso_code
)
where loanRank <= 5
  and loan_count is not null
order by country, loanRank;









------------------------------------------------------------------------------------------------------------------------
--009 - Uganda be kidding me
------------------------------------------------------------------------------------------------------------------------




set define off;

--Purpose: Download all the loans for the GLBT team and count the number of loans made to Uganda.
declare
  httpuri HttpUriType;
  teamLendersXML xmltype;
  numberOfPages number;
  numberOfUgandaLoans number := 0;
  country varchar2(100);
  loanIndex number;

  function extractAndGetStringVal(xml in xmltype, xpath in varchar2) return varchar2 is
    tempXML xmltype;
  begin
      tempXML := xml.extract(xpath);
      if tempXML is null then
        return null;
      else
        return tempXML.getStringVal();
      end if;
  end extractAndGetStringVal;

begin
  --Load the first page to get the number of pages
  httpuri := HttpUriType('http://api.kivaws.org/v1/teams/147/loans.xml&page=1&app_id=org.kivadata');
  teamLendersXML := httpuri.getXML();

  --Get the number of pages
  numberOfPages := extractAndGetStringVal(teamLendersXML, '/response/paging/pages/text()');

  --Loop through all of the pages
  for pageNumber in 1 .. numberOfPages loop

    --Get the new page (don't get the first page again)
    if pageNumber <> 1 then
      httpuri := HttpUriType('http://api.kivaws.org/v1/teams/147/loans.xml&page='||pageNumber||'&app_id=org.kivadata');
      teamLendersXML := httpuri.getXML();
    end if;

    loanIndex := 0;  
    --Loop through all loans
    loop
      --increment loan index
      loanIndex := loanIndex + 1;

      --Get the country name
      country := extractAndGetStringVal(teamLendersXML, '/response/loans/loan['||loanIndex||']/location/country/text()');

      --If there is no name, there are no more loans so exit the loop
      exit when country is null;

      --Look for Uganda loans
      if country = 'Uganda' then
        numberOfUgandaLoans := numberOfUgandaLoans + 1;
      end if;
    end loop; --end looping through loans on a page

  end loop; --end looping through pages

  --Display results
  dbms_output.put_line('Number of loans to Uganda = '||numberOfUgandaLoans);
end;
/




/




/*




I've made many loans to Uganda and it upset me to learn that homosexuality in Uganda may soon be a capital crime.
Most of the people who receive Kiva loans live in developing nations, and it seems inevitable that countries with
developing economies will also be developing human rights.  But it's amazing to see any country think about taking
such a huge step back.

As of about a week ago, 76,828 people have used Kiva to loan $6,293,700 to 23,285 entrepreneurs in Uganda.  
Most of those lenders would probably also be upset to learn about what's happening in Uganda.
Kiva is about eradicating poverty by creating personal connections 
between lenders and entrepreneurs, I wonder if there's some way to use that connection here.

Some people have suggested different countries or groups should withold funding from Uganda, but but I don't think 
that kind of idea would work on Kiva.  Maybe mass commenting on the journal entries, but that would probably just be
spamming.






http://en.wikipedia.org/wiki/LGBT_rights_by_country_or_territory


http://www.google.com/search?&q=uganda+gay+rights

I've lent over $2,000 to entrepreneurs in Uganda.


Number of loans to Uganda = 473
on December 29, 2009


According to http://www.kiva.org/community, GLBT has
1768 members, 12313 loans, for $361,975.00

But the API only says there are 9991 loans

This would mean an average of $36.23 per loan.

Minimum loaned is $11,075
But $17,137 is probably closer to the real value


*/

select * from loan where country = 'Uganda'
and loan.id in (select loan_id from loan_lender where lender_id = 'jonandlisa')



$6,293,700 loaned
76,828 lenders
4,209 loans
23,285


select sum(loan_amount) from loan where country = 'Uganda';

select count(*) from loan where country = 'Uganda';


select count(distinct lender_id) from loan_lender where loan_id in (select id from loan where country = 'Uganda');

select count(*) from borrower where loan_id in (select id from loan where country = 'Uganda');



------------------------------------------------------------------------------------------------------------------------
--010 - point in time partners information (Thiemo)
------------------------------------------------------------------------------------------------------------------------


--Get a running-total amount raised and loans posted up to the end of each month.
--Some of these numbers are probably off because my data does not include refunded loans.
select partner.name, partner.id partner_id
  --Get the running totals
  ,months.yearAndMonth
  ,sum(loans.total_amount_raised) over (partition by partner.name order by months.yearAndMonth)
    total_amount_raised
  ,sum(loans.loans_posted) over (partition by partner.name order by months.yearAndMonth)
    loans_posted
from
  (
    --Create a month from the first posted_date until the last month of data
    select to_char(add_months((select max(posted_date) from loan), -(level)), 'YYYYMM') yearAndMonth
    from dual
    connect by level <= months_between((select max(posted_date) from loan), (select min(posted_date) from loan))+1
  ) months
    cross join partner
    left outer join
  (
    --For each partner and month, count the number of loans and the amount of money raised
    select partner_id, to_char(posted_date, 'YYYYMM') yearAndMonth
      ,sum(loan_amount) total_amount_raised
      ,count(loan.id) loans_posted
    from loan
    group by partner_id, to_char(posted_date, 'YYYYMM')
  ) loans on partner.id = loans.partner_id and months.yearAndMonth = loans.yearAndMonth
order by partner.name, months.yearAndMonth;









------------------------------------------------------------------------------------------------------------------------
--011 - Anonymous lender information (screen scraping) (Brian Duncan)
------------------------------------------------------------------------------------------------------------------------





create or replace directory loanHTML as 'D:\Backups and Exports\Loan HTML';
set serveroutput on size 1000000
set define off



--Download loan HTML files from Kiva.org to get some information that is not available through the API.
declare
  httpuri HttpUriType;
  loanClob Clob;
  url varchar2(100);
  failCounter number;
begin
  --Iterate through the loans in order
  for loans in
  (
    --Something like this can help with loading new loans
    --select id from kiva2.loan where status <> 'fundraising'
    --minus
    --select id from kiva.loan where status <> 'fundraising'
    --order by id
    --This is for loading all loans
    select id from loan
    where status <> 'fundraising'
    --  and rownum <= 1 --for testing
    order by loan.id
  ) loop
    failCounter := 0;
    <<start_loop>>

    --Create URL
    url := 'http://www.kiva.org/app.php?page=businesses&action=about&id='||loans.id;

    --Create temporary CLOB
    dbms_lob.createTemporary(loanClob, true);
    
    --Get the CLOB
    httpuri := HttpUriType(url);
    
    --This line will frequently fail.  If there is a problem, wait a few seconds and try again.
    begin
      loanClob := httpuri.getClob();
    exception when others then
      failCounter := failCounter + 1;
      if failCounter >= 3 then
        dbms_output.put_line('Failed 3 times for loan.id '||loans.id);
        --raise
        goto end_loop;
      end if;
      sys.dbms_lock.sleep(2);
      goto start_loop;
    end;

    --Write the CLOB to a file.  (I think there may be some newline issues here, but that probably won't matter)
    dbms_xslProcessor.clob2File(loanClob, 'LOANHTML', loans.id||'.html');

    --Free the LOB
    dbms_lob.freeTemporary(loanClob);

    --Wait half a second between calls
    --This doesn't actually help, the server still throws an error just as quickly.
    --sys.dbms_lock.sleep(0.5);

    <<end_loop>>
    null;
  end loop;
end;
/





--Insert a large number of anonymous lenders into loans
--This block is meant to work with the results from Insert Anonymous Lenders.pl
--That file creates a comma-separated list of numbers that must be pasted into this block.
--However, due to some program size limits, only a small number of data can be pasted at once.
--(There must be a better way to do this, but I may never need to do this again so it probably doesn't matter.)
declare
  type loan_id_table is table of number;
  loan_ids loan_id_table := loan_id_table();
begin
  --Create a nested table to hold all of the loan_ids
  --Test:
  --loan_ids := loan_id_table(1, 1, 2, 3);
  loan_ids := loan_id_table(
100,1000,1000,1000,1000,1000,1000,1000,1000,1000,100004,100004,100006,100006,100006,100006,100006,100007,100007,100007,100007,100007,100007,100007,100008,100008,100008,100008,100009,10001,10001,10001,100014,100014,100014,100015,100017,100018,100020,100021,100023,100023,100023,100023,100023,100024,100024,100024,100025,100025,100026,100026,100026,100027,100027,100027,100028,100029,100029,10003,100031,100033,100034,100036,100036,100036,100036,100036,100038,100038,100039,100039,100039,100039,100041,100041,100041,100041,100041,100041,100041,100042,100043,100046,100046,100046,100046,100048,100048,100049,100049,100051,100051,100054,100054,100055,100057,100059,10006,10006
,10006,100060,100060,100060,100061,100062,100063,100064,100065,100065,100065,10007,100070,100071,100072,100073,100073,100073,100073,100074,100074,100076,100076,100076,100078,100078,100079,100079,100079,100079,100079,10008,10008,10008,100080,100080,100081,100083,100083,100083,100084,100085,100085,100087,100088,10009,100092,100092,100092,100094,100094,100094,100094,100094,100094,100094,100095,100095,100095,1001,100100,100102,100102,100103,100105,100105,100105,100105,100105,100105,100106,100106,100110,100111,100111,100111,100111,100111,100112,100113,100115,100116,100116,100120,100120,100121,100122,100122,100123,100123,100125,100125,100125,100126,100127,100127,100127,100127,100128,100128
,100128,100129,10013,100130,100131,100131,100132,100134,100135,100136,100137,100138,100138,100138,100138,100138,100138,100138,100138,100139,10014,10014,10014,100142,100142,100143,100143,100145,100145,100145,100149,10015,10015,10015,100150,100150,100153,100153,100154,100154,100154,100155,100155,100155,100155,100156,100156,100158,100158,100158,100158,100159,100159,100159,100159,100159,100159,10016,100160,100160,100161,100161,100161,100161,100161,100162,100162,100162,100162,100162,100162,100163,100163,100163,100163,100163,100164,100164,100164,100165,100166,100166,100167,100167,100167,100168,100168,100168,100168,100169,100170,100170,100170,100170,100170,100170,100171,100171,100171,100171
,100171,100171,100172,100172,100172,100172,100172,100173,100173,100173,100174,100174,100174,100174,100174,100175,100175,100175,100176,100176,100176,100176,100176,100176,100177,100177,100177,100177,100177,100177,100179,10018,100181,100182
);
  forall i in 1 .. loan_ids.count
    insert into loan_lender(loan_id, lender_id) values(loan_ids(i), 'Anonymous');
end;
/



--Export to CSV
begin
  data_dump(query_in => 'select * from loan_lender order by loan_id, lenderOrder nulls last', file_in => 'loan_lender_with_anonymous.csv'
    ,directory_in => 'EXPORTDIRECTORY', delimiter_in => ',', surrounding_char_in => '"', convert_html_in => true);
end;
/




select * from loan_lender where lender_id = 'Anonymous';
rollback;

2645567


select count(*) from loan_lender;















------------------------------------------------------------------------------------------------------------------------
--012 - Countries Loaned Too
------------------------------------------------------------------------------------------------------------------------

select * from lender;

set define off


--Color each country for a lender
select '<img src=\"http://chart.apis.google.com/chart?'|| --Google charts
  'cht=t&chtm=world'|| --map chart type
  '&chs=440x220'|| --maximum map size
  '&chd=t:'||colorLevels|| --color levels, always 100 - all countries are the same color
  '&chco=FFFFFF,000000,FFFF00,00FF00'|| --countries with loans are green, else white
  '&chld='||isoCodes|| --comma separated list of countries
  '&chf=bg,s,EAF7FE\">' my_countries --set the water color to light blue
from
(
  select
    to_string2(cast(collect(iso_code order by iso_code) as varchar2_ntt), '') isoCodes
    ,to_string2(cast(collect(to_char(colorLevel)) as varchar2_ntt)) colorLevels
  from
  (
    select distinct iso_code, 100 colorLevel
    from loan
      inner join country on loan.country = country.name
    where id in (select loan_id from loan_lender where lender_id = 'jonandlisa')
  )
);



--Current Loans
select '<img src=\"http://chart.apis.google.com/chart?'|| --Google charts
  'cht=t&chtm=world'|| --map chart type
  '&chs=440x220'|| --maximum map size
  '&chd=t:'||colorLevels|| --color levels, always 100 - all countries are the same color
  '&chco=FFFFFF,000000,FFFF00,00FF00'|| --countries with loans are green, else white
  '&chld='||isoCodes|| --comma separated list of countries
  '&chf=bg,s,EAF7FE\">' my_countries --set the water color to light blue
from
(
  select
    to_string2(cast(collect(iso_code order by iso_code) as varchar2_ntt), '') isoCodes
    ,to_string2(cast(collect(to_char(colorLevel)) as varchar2_ntt)) colorLevels
  from
  (
    select distinct iso_code, 100 colorLevel
    from loan
      inner join country on loan.country = country.name
    where id in (select loan_id from loan_lender where lender_id = 'jonandlisa')
      and status 
  )
);

select distinct status from loan



--Numaber of countries: 52
select count(distinct country) from loan

--Numaber of countries per lender
select numberOfCountries, count(*) frequency
from
(
  select loan_lender.lender_id, count(distinct loan.country) numberOfCountries
  from loan_lender
    inner join loan on loan_lender.loan_id = loan.id
  group by loan_lender.lender_id
)
group by numberOfCountries
order by numberOfCountries desc


--Only Anonymous has lent to all 52 countries.  But there are 7 people who lent to 51 countries... so close!
select loan_lender.lender_id, count(distinct loan.country) numberOfCountries
from loan_lender
  inner join loan on loan_lender.loan_id = loan.id
group by loan_lender.lender_id
order by numberOfCountries desc


--Find the missing country for the lenders with 51/52 countries
--List of relevant lenders with all countries
--All of these lenders are missing Gaza.
select country, lender_id
from
  (select distinct country from loan) countries
  cross join
  (
    select 'DianeR' lender_id from dual union all
    select 'HMSBeagle' from dual union all
    select 'cjw' from dual union all
    select 'tim9172' from dual union all
    select 'kay1272' from dual union all
    select 'michael14567121' from dual union all
    select 'howard4162' from dual
  )
minus
--All countries for the 51/52 lenders
select distinct loan.country, lender_id
from loan_lender
  inner join loan on loan_lender.loan_id = loan.id
where loan_lender.lender_id in ('DianeR','HMSBeagle','cjw','tim9172','kay1272','michael14567121','howard4162')






--Numaber of loans per lender
select numberOfLoans, count(*) frequency
from
(
  select loan_lender.lender_id, count(*) numberOfLoans
  from loan_lender
  group by loan_lender.lender_id
)
group by numberOfLoans
order by numberOfLoans desc






--Color each country for a lender, set the color intensity to the percent of
--all loans to that country

--
-- DOES NOT WORK WELL
--
select 'http://chart.apis.google.com/chart?'|| --Google charts
  'cht=t&chtm=world'|| --map chart type
  '&chs=440x220'|| --maximum map size
  '&chd=t:'||colorLevels|| --color levels, always 100 - all countries are the same color
  
  --FFFFFF,FF0000,FFFF00,00FF00  works kinda ok for intensity

  '&chco=FFFFFF,000000,FFFF00,00FF00'|| --countries with loans are green, else white
  '&chld='||isoCodes|| --comma separated list of countries
  '&chf=bg,s,EAF7FE' --set the water color to light blue
from
(
  select
    to_string2(cast(collect(iso_code order by iso_code) as varchar2_ntt), '') isoCodes
    ,to_string2(cast(collect(to_char(colorLevel)) as varchar2_ntt)) colorLevels
  from
  (
    select iso_code, trim(to_char(ratio_to_report(total) over () * 100, '999')) colorLevel
    from
    (
      select iso_code, count(*) total
      from loan
        inner join country on loan.country = country.name
      where id in (select loan_id from loan_lender where lender_id = 'jonandlisa')
      group by iso_code
    )
  )
)
;















------------------------------------------------------------------------------------------------------------------------
--013: Data for Mina Nishisaka (number of loans, etc)
------------------------------------------------------------------------------------------------------------------------

--Number of loans and amount per day
select days.funded_date
  ,nvl(loansPerDay.loans_per_day, 0) loans_per_day
  ,nvl(total_loan_amount, 0) total_loan_amount
  ,sum(nvl(loansPerDay.loans_per_day, 0)) over (order by days.funded_date) cumulative_loans_per_day
  ,sum(nvl(loansPerDay.total_loan_amount, 0)) over (order by days.funded_date) cumulative_loan_amount
from
(
  --All the days from the first to last funded date
  select (select trunc(max(funded_date)) from loan) - (level - 1) funded_date from dual
  connect by level <= (select ceil(max(funded_date) - min(funded_date)) from loan) + 1
) days
left outer join
(
  select trunc(funded_date) funded_date, count(*) loans_per_day, sum(loan_amount) total_loan_amount
  from loan
  where funded_date is not null --don't include loans not funded yet
  group by trunc(funded_date)
) loansPerDay
on days.funded_date = loansPerDay.funded_date
order by days.funded_date desc;



--Number of loans and amount per month
select days.funded_month
  ,nvl(loansPerDay.loans_per_day, 0) loans_per_month
  ,nvl(total_loan_amount, 0) total_loan_amount
  ,sum(nvl(loansPerDay.loans_per_day, 0)) over (order by days.funded_month) cumulative_loans_per_month
  ,sum(nvl(loansPerDay.total_loan_amount, 0)) over (order by days.funded_month) cumulative_loan_amount
from
(
  --All the months from the first to last funded date
  select distinct(to_char(funded_date, 'YYYYMM')) funded_month
  from
  (
    select (select trunc(max(funded_date)) from loan) - (level - 1) funded_date from dual
    connect by level <= (select ceil(max(funded_date) - min(funded_date)) from loan) + 1
  )
) days
left outer join
(
  select to_char(funded_date, 'YYYYMM') funded_month, count(*) loans_per_day, sum(loan_amount) total_loan_amount
  from loan
  where funded_date is not null --don't include loans not funded yet
  group by to_char(funded_date, 'YYYYMM')
) loansPerDay
on days.funded_month = loansPerDay.funded_month
order by days.funded_month desc;









select count(*) from loan where funded_date is not null;
select sum(loan_amount) from loan where funded_date is not null;




select * from v_summary_cumulativeloans;
--ALSO USED IN HOME PAGE
select 
  case when rownumber = 1 then 'data.addRows('||to_char(numberOfRows)||');' else null end ||
  --Display all months, let Google Charts API sort out which labels to print
  'data.setValue('||to_char(rownumber-1)||', 0, "'||to_char(to_date(yearAndMonth, 'YYYYMM'), 'Mon YYYY')||'");'||
  'data.setValue('||to_char(rowNumber-1)||', 1, '||total||');' javascript
from
(
  select yearAndMonth, sum(total) over (order by yearAndMonth rows between unbounded preceding and current row) total
    ,row_number() over (order by yearAndMonth) rownumber, count(total) over () numberOfRows
  from
  (
    select months.yearAndMonth, count(loans.yearAndMonth) total
    from
      (select yearAndMonth from mv_months) months
      ,(select to_char(disbursal_date, 'YYYYMM') yearAndMonth from loan
        where disbursal_date is not null) loans --two loans don't have a date, and will throw off results
    where months.yearAndMonth = loans.yearAndMonth (+)
    group by months.yearAndMonth
    order by months.yearAndMonth
  )
);

















------------------------------------------------------------------------------------------------------------------------
--014: Vikram data request 2
------------------------------------------------------------------------------------------------------------------------

/*1: Is there a way to get historical partner default rates?

See the perl script D:\Kiva\scripts and old files\Get Default Dates.pl
That file generated results, but some more data had to be loaded

*/


create or replace directory loanHTML2 as 'D:\Backups and Exports\Loan HTML for default dates';
set serveroutput on size 1000000
set define off



--Download loan HTML files from Kiva.org to get some information that is not available through the API.
declare
  httpuri HttpUriType;
  loanClob Clob;
  url varchar2(100);
  failCounter number;

  type number_tab is table of pls_integer;
  defaultedLoans number_tab;
  
  v_loan_id pls_integer;
begin
  defaultedLoans := number_tab(
    10176,10177,10178,10746,11530,11540,13505,13558,13560,16544,16545,16546,16547,18684,18686,18688,
    18699,22479,22483,23143,24918,24928,24934,24974,25116,25118,25122,25190,25448,25459,25552,27069,
    27077,27079,27083,27088,27089,27096,27433,28151,28155,32205,33770,33817,33820,33825,33967,33980,
    38669,38891,38893,38895,38909,38914,38936,38977,39703,41996,44270,44275,44276,44277,44387,45455,
    45457,4566,46178,46312,46313,46314,46333,46390,46392,46930,47242,49282,49283,49284,49285,49309,
    49313,49324,50589,50592,5143,51903,51905,51910,51911,51914,51979,52577,52579,52581,5409,54205,
    57576,57580,57582,57586,57587,57588,57589,57590,57591,57593,57594,5935,6079,61226,61231,61504,64104,
    64112,64507,64536,6484,65682,65687,65691,65693,65697,65704,65713,69275,69281,69285,69286,69288,
    69293,69294,69298,69301,7061,7085,7089,7326,7423,75234,75236,75315,7562,80050,9544,9549
  );

  --Iterate through the loans in order
  for i in 1 .. defaultedLoans.count loop
    v_loan_id := defaultedLoans(i);

    failCounter := 0;
    <<start_loop>>

    --Create URL
    url := 'http://www.kiva.org/app.php?page=businesses&action=about&id='||v_loan_id;

    --Create temporary CLOB
    dbms_lob.createTemporary(loanClob, true);
    
    --Get the CLOB
    httpuri := HttpUriType(url);
    
    --This line will frequently fail.  If there is a problem, wait a few seconds and try again.
    begin
      loanClob := httpuri.getClob();
    exception when others then
      failCounter := failCounter + 1;
      if failCounter >= 3 then
        dbms_output.put_line('Failed 3 times for loan.id '||v_loan_id);
        --raise
        goto end_loop;
      end if;
      sys.dbms_lock.sleep(2);
      goto start_loop;
    end;

    --Write the CLOB to a file.  (I think there may be some newline issues here, but that probably won't matter)
    dbms_xslProcessor.clob2File(loanClob, 'LOANHTML2', v_loan_id||'.html');

    --Free the LOB
    dbms_lob.freeTemporary(loanClob);

    --Wait half a second between calls
    --This doesn't actually help, the server still throws an error just as quickly.
    --sys.dbms_lock.sleep(0.5);

    <<end_loop>>
    null;
  end loop;
end;
/



--The script above created D:\Kiva\Adhoc results\default dates.sql

--Create this table...
create table default_date
(
  loan_id number,
  default_date date
);


--Then take the above SQL statements and embed them in this:

insert into default_date
select loan_id, to_date(default_date, 'Mon DD, YYYY') from
(
select 5494 loan_id, 'Jun 5, 2008' default_date from dual union all 
...
);


----
--NOTE: I no longer need to scrap the HTML, default_date is avaialble through the API.
----


--Number of defaulted loans
select partner_id, default_date
  ,sum(defaultedLoans) over (partition by partner_id order by default_date) running_default_count
from
(
  select partner_id, default_date, count(*) defaultedLoans
  from
  (
    select partner_id, default_date, loan_id
    from default_date
      inner join loan on default_date.loan_id = loan.id
  ) loans_and_dates
  group by default_date, partner_id
  order by partner_id, default_date
) loans_grouped_by_date
;


--Number of loans per partner
select partner_id, posted_date
  ,sum(numberOfLoans) over (partition by partner_id order by posted_date) running_loan_count
from
(
    select partner_id, posted_date, count(*) numberOfLoans
    from loan
    group by partner_id, posted_date
) 
order by partner_id, posted_date


--Compare the last posted date with the first default date
select posted_dates.partner_id, last_posted_date, default_dates.first_default_date
  ,case
    when first_default_date is null then ''
    when last_posted_date > first_default_date then 'true'
    else 'false'
  end loans_after_first_default
from
  (
    --When is the last loan per partner
    select partner_id, max(posted_date) last_posted_date
    from loan
    group by partner_id
    order by partner_id
  ) posted_dates
  left outer join
  (
    --When is the first default date per partner
    select loan.partner_id, min(loan.defaulted_date) first_default_date
    from loan
    where status = 'defaulted'
    group by loan.partner_id
    order by partner_id
  ) default_dates
    on posted_dates.partner_id = default_dates.partner_id
order by posted_dates.partner_id


--Most partners that ever had a default had their default occur after their last loan

--Partners with loans after first default (5): 3,18,40,76,101
--Partners with no loans after first default (11): 1,2,6,7,25,27,32,36,37,45,66


--1,2,3,6,7,18,25,27,32,36,37,40,45,66,76,101
select distinct partner_id from loan where status = 'defaulted';







--For each loan (that belongs to a partner that has any loans posted after a default), what was the partner's
--default rate when the loan was posted.
--Default rate is the amount of money defaulted (not paid back) / amount of ended loans
with default_dates as
(
  --Create a timeline of amount defaulted for partners that posted loans after their first default
  --Not sure how to deal with the exact date of default.  I'll assume that default rates don't go into effect
  --until the next day.  (Someone on the forum confirmed this)
  select partner_id, defaulted_date, running_default_count, running_total_amount_lost
    ,case
      when last_defaulted_date is null then defaulted_date --first row, start with early date
      else defaulted_date + 1
    end start_defaulted_date
    ,nvl(next_defaulted_date, to_date('01-JAN-9999', 'DD-MON-YYYY')) end_defaulted_date
  from
  (
    --Add the previous and the next default date to each row
    select partner_id, defaulted_date, running_default_count, running_total_amount_lost
      ,lag(defaulted_date) over (partition by partner_id order by defaulted_date) last_defaulted_date
      ,lead(defaulted_date) over (partition by partner_id order by defaulted_date) next_defaulted_date
    from
    --Running total amount of money defaulted per partner, with before and after row
    (
      --get a running total for amount defaulted per partner
      select partner_id, defaulted_date
        ,sum(defaultedLoans) over (partition by partner_id order by defaulted_date) running_default_count
        ,sum(total_amount_lost) over (partition by partner_id order by defaulted_date) running_total_amount_lost
      from
      (
        select partner_id, defaulted_date, count(*) defaultedLoans, sum(total_amount_lost) total_amount_lost
        from
        (
          select partner_id, trunc(defaulted_date) defaulted_date
            ,count(*) numberOfLoans
            ,sum(loan_amount - nvl(payments.total_amount, 0)) total_amount_lost
            --,sum(loan_amount) amount_owed
            --,sum(nvl(payments.total_amount, 0)) amount_paid
          from loan
            left outer join
            (
              select loan_id, sum(amount) total_amount
              from payment
              group by loan_id
            ) payments on loan.id = payments.loan_id
          where partner_id in (3,18,40,76,101)
            and status = 'defaulted'
          group by partner_id, trunc(defaulted_date)
        ) loans_and_dates
        group by defaulted_date, partner_id
        order by partner_id, defaulted_date
      ) loans_grouped_by_date
      ---------
      union all
      ---------
      --Create a before row for each partner
      select partner_id, defaulted_date, running_default_count, running_total_amount_lost
      from
      (
        select to_date('01-JAN-1000', 'DD-MON-YYYY') defaulted_date, 0 running_default_count
          ,0 running_total_amount_lost from dual
      ) before_rows
      cross join
      (
        select 3 partner_id from dual union all
        select 18 partner_id from dual union all
        select 40 partner_id from dual union all
        select 76 partner_id from dual union all
        select 101 partner_id from dual
      ) partners
      order by partner_id, defaulted_date
    )
    order by partner_id, defaulted_date
  )
  order by partner_id, defaulted_date
),
------------------------------------------------------------------------------------------------------------------------
end_dates as
(
  --Create a timeline of number of ended loans for partners that posted loans after their first default
  --Not sure how to deal with the exact date of the loan ending.  I'll assume that rates don't go into effect
  --until the next day.
  select partner_id, end_date, running_loan_count, running_total_amount
    ,case
      when last_end_date is null then end_date
      else end_date + 1
    end start_end_date
    ,nvl(next_end_date, to_date('01-JAN-9999', 'DD-MON-YYYY')) end_end_date
  from
  (
    --Add the previous and the next default date to each row
    select partner_id, end_date, running_loan_count, running_total_amount
      ,lag(end_date) over (partition by partner_id order by end_date) last_end_date
      ,lead(end_date) over (partition by partner_id order by end_date) next_end_date
    from
      --Running total of defaulted loans per partner, with before and after row
      (
      --Number of ended loans per partner for partners with loans after first default
      select partner_id, end_date
        ,sum(numberOfLoans) over (partition by partner_id order by end_date) running_loan_count
        ,sum(total_amount) over (partition by partner_id order by end_date) running_total_amount
      from
      (
          select partner_id, nvl(trunc(paid_date), trunc(defaulted_date)) end_date, count(*) numberOfLoans
            ,sum(loan_amount) total_amount
          from loan
          where partner_id in (3,18,40,76,101)
            and status in ('defaulted', 'paid')
          group by partner_id, nvl(trunc(paid_date), trunc(defaulted_date))
      ) 
      ---------
      union all
      ---------
      --Create a before row for each partner
      select partner_id, end_date, running_loan_count, running_total_amount
      from
      (
        select to_date('01-JAN-1000', 'DD-MON-YYYY') end_date, 0 running_loan_count, 0 running_total_amount from dual
      ) before_rows
      cross join
      (
        select 3 partner_id from dual union all
        select 18 partner_id from dual union all
        select 40 partner_id from dual union all
        select 76 partner_id from dual union all
        select 101 partner_id from dual
      ) partners
      order by partner_id, end_date
    )
    order by partner_id, end_date
  )
  order by partner_id, end_date
)
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
select
  loan.id loan_id
  ,loan.partner_id
  ,to_char(loan.posted_date, 'DD-Mon-YYYY') posted_date
  ,case when running_total_amount_lost = 0 then 'n/a'
    else trim(to_char(default_dates.running_total_amount_lost / end_dates.running_total_amount * 100, '990.999'))
  end defaulted_percent
  ,default_dates.running_total_amount_lost running_total_default_amount
  ,default_dates.running_default_count
  ,to_char(default_dates.start_defaulted_date, 'DD-Mon-YYYY') start_defaulted_date
  ,to_char(default_dates.end_defaulted_date, 'DD-Mon-YYYY') end_defaulted_date
  ,end_dates.running_total_amount running_total_end_amount
  ,end_dates.running_loan_count running_loan_end_count
  ,to_char(end_dates.start_end_date, 'DD-Mon-YYYY') start_end_date
  ,to_char(end_dates.end_end_date, 'DD-Mon-YYYY') end_end_date
from loan
  inner join default_dates on loan.partner_id = default_dates.partner_id
    and loan.posted_date between default_dates.start_defaulted_date and default_dates.end_defaulted_date
  inner join end_dates on loan.partner_id = end_dates.partner_id
    and loan.posted_date between end_dates.start_end_date and end_dates.end_end_date
where partner_id in (3,18,40,76,101)
order by partner_id, loan.id;





3: Default Rate:  	 14.46% 
Entrepreneurs: 38 on partner page, 37 in API

--36,700 (matches partner page)
select sum(loan_amount) from loan where partner_id = 3

--6325
select sum(loan_amount) from loan where partner_id = 3 and status = 'defaulted'


--151,392,395,700,1346,3063
select * from loan where partner_id = 3 and status = 'defaulted'

--Sum of amount = 1020
select * from payment where loan_id in (151,392,395,700,1346,3063)

--Total amount = 6325 - 1020 = 5305 missing

5305 / 36,700 = 14.455



Default rate of 14.46
This implies that 5,306.82 is missing





/* 18:

Default rate of 0.28% - http://www.kiva.org/partners/18

2030 total loans - http://api.kivaws.org/v1/loans/search.xml&partner=18

2 fundraising - http://api.kivaws.org/v1/loans/search.xml&partner=18&status=fundraising
11 defaulted - http://api.kivaws.org/v1/loans/search.xml&partner=18&status=defaulted
1202 paid back - http://api.kivaws.org/v1/loans/search.xml&partner=18&status=paid
2 funded - http://api.kivaws.org/v1/loans/search.xml&partner=18&status=funded
813 in_repayment - http://api.kivaws.org/v1/loans/search.xml&partner=18&status=in_repayment

2030

11 / 2030 = 0.542
11 / (1202 + 11) = 0.907

All of the 11 defaulted loans defaulted in December 2009.

Where does the 0.28% come from?

(11 / x) * 100 = 0.28
11/x = 0.28/100
11 = 0.0028x
3,929 loans?


December 2009

*/



/* 101:

Default rate of 0.07% - http://www.kiva.org/partners/101

576 total loans - http://api.kivaws.org/v1/loans/search.xml&partner=101

0 fundraising - http://api.kivaws.org/v1/loans/search.xml&partner=101&status=fundraising
2 defaulted - http://api.kivaws.org/v1/loans/search.xml&partner=101&status=defaulted
511 paid back - http://api.kivaws.org/v1/loans/search.xml&partner=101&status=paid
0 funded - http://api.kivaws.org/v1/loans/search.xml&partner=101&status=funded
63 in_repayment - http://api.kivaws.org/v1/loans/search.xml&partner=101&status=in_repayment

2/576 = 0.347222%
2/513 = 0.389864%

select id from loan where partner_id = 101 and status = 'defaulted'

Defaulted April 2009, May 2009

*/







/*

partner default rate


I'm having some difficulty calculating historical partner default rates.

First, I am unable to reproduce the current default rates.  According to the knowledge base link on the Default Rate 
label, "Partner Default Rate = Amount of ended loans defaulted / Amount of ended loans."  But when I try to calculate 
this with the API my numbers don't work.  For example, partner 18 has a default rate of 0.28% 
[http://www.kiva.org/partners/18], has 11 defaulted loans 
[http://api.kivaws.org/v1/loans/search.xml&partner=18&status=defaulted] and 1202 loans paid back
[http://api.kivaws.org/v1/loans/search.xml&partner=18&status=paid].  I calculate 11 / (1202 + 11) * 100 = 0.91%.  
I thought the issue might be dividing by all loans instead of just ended loans, but even using 2030 total loans 
[http://api.kivaws.org/v1/loans/search.xml&partner=18] I still get 0.54%.  The defaulted loans all defaulted in 
December 2009, so I'm guessing it's not just a short term data refresh issue.  I found a similar issue with partner 
101.  Am I calculating this wrong?

Also, I don't see a default date in the API so I used data from the HTML pages.  Some loans have a "Defaulted Date" 
but others only have an "Ended Date".  Are they the same thing for a defaulted loan?  How often are the partner 
statistics updated?  Would the partner's default rate change the same day they have a defaulted loan?  And would it 
change on both the partner page and in the "About Field Partner" section on the loan page?


Thanks,
Jon
*/


--partners 1, 5, and 7 have some paid loans without a paid_date.  Those partner's aren't relevant here.
select * from loan where status = 'paid' and paid_date is null;



--No rows
select * from loan where status = 'defaulted' and defaulted_date is null;

--There is only one row where the status is not defaulted but there is a default date
select partner_id from loan where status <> 'defaulted' and defaulted_date is not null;

--About 700 of the dates differ, but they only differ by a day
select loan.id, loan.defaulted_date, default_date.default_date, loan.defaulted_date - default_date.default_date difference
from loan inner join default_date on loan.id = default_date.loan_id
where status = 'defaulted'
  and trunc(loan.defaulted_date) <> trunc(default_date.default_date)





--2: Why do some loans take a long time to end

select loan.id, partner_id, status, paid_date, defaulted_date
from loan where extract(year from posted_date) = 2006
order by paid_date desc, defaulted_date desc, loan.id


select loan.id, partner_id, status, paid_date, defaulted_date, disbursal_date
from loan where extract(year from posted_date) = 2007
order by paid_date desc, defaulted_date desc, loan.id






















------------------------------------------------------------------------------------------------------------------------
--015: Unluckiest lender
------------------------------------------------------------------------------------------------------------------------



--Number of lenders with all defaulted loans, per number of loans
select total_loans, count(*) from
(
  --Lenders with the most defaulted loans
  select lender_id, total_loans, defaulted_loans from
  (
    select loan_lender.lender_id, count(*) total_loans
      ,sum(case when loan.status = 'defaulted' then 1 else 0 end) defaulted_loans
    from loan_lender
      inner join loan on loan_lender.loan_id = loan.id
    group by loan_lender.lender_id
  )
  where total_loans = defaulted_loans
  order by defaulted_loans desc
)
group by total_loans
order by total_loans desc;


select * from loan_lender where lender_id = 'jennie9056'
--5650
select sum(loan_amount) from loan where loan.id in (1511,1512,1513,1515,1517,1518,1519,1520,1521)
--4867
select sum(amount) from payment where loan_id in (1511,1512,1513,1515,1517,1518,1519,1520,1521)

--86.14% of money was repaid
select 4867 / 5650 * 100 from dual;






/*

The unluckiest lenders

The odds of getting repaid are very good.  Only about 3.5% of loans default, and even then the chances are good you'll
still get most of your money back.  But with over 450,000 of lenders there are bound to be some unlucky people.
The unluckiest lender had all of her 9 loans default.  She also had 11 refunded loans.  Usually refunded loans aren't
included in Kiva's numbers or in their API, but I noticed them on her lender page.  At least those 9 loans
repaid a significant fraction of the loan amount, so she probably got about 86% of her money back.

Here's a list of the number of lenders with 100% defaulted loans:

# Loans   # Lenders
9	        1
6	        1
5         3
4         12
3         30
2         264
1         4492


I like to tell people how awesome Kiva is, but these people probably tell their friends that Kiva is a huge failure.  
If I'm going to loan more money anyway, I might as well try to help two unlucky people at one time.  I just sent a message
to the unluckiest lender offering to send her a gift certificate to make a new loan, hopefully I can help change
a statistic instead of just calculating it.



*/


select status, count(*) from loan group by status order by status desc


--3523
select count(*) from loan where status = 'defaulted';

--99345
select count(*) from loan where status in ('paid', 'defaulted');

--3.5% of loans default
select 3523 / 99345 * 100 from dual;

















------------------------------------------------------------------------------------------------------------------------
--016: Friso Klok - business development
------------------------------------------------------------------------------------------------------------------------


--How do businesses develop.
--Track businesses across loans by grouping by name, country, town, and sector.
--number_of_loans, loan_amount, and number_of_borrowers may be signs of business growth.
--Summary of differences
select
  count(*) businesses_with_multiple_loans
  --Number of businesses that ever had a loan amount increase, decrease, or stay the same
  ,sum(increased_amount) increased_amount
  ,sum(static_amount) static_amount
  ,sum(decreased_amount) decreased_amount
  --Number of businesses that had specific combinations of loan amounts increasing, decreasing, or staying the same
  ,sum(case when increased_amount = 1 and decreased_amount = 0 then 1 else 0 end) increased_static_amount_only
  ,sum(case when decreased_amount = 1 and increased_amount = 0 then 1 else 0 end) decreased_static_amount_only
  ,sum(case when static_amount = 1 and decreased_amount = 0 and increased_amount = 0 then 1 else 0 end) static_amount_only
  --Number of businesses that ever had number of borrowers increase, decrease, or stay the same
  ,sum(increased_borrowers) increased_borrowers
  ,sum(static_borrowers) static_borrowers
  ,sum(decreased_borrowers) decreased_borrowers
  --Number of businesses that had specific combinations of loan amounts increasing, decreasing, or staying the same
  ,sum(case when increased_borrowers = 1 and decreased_borrowers = 0 then 1 else 0 end) increased_static_borrower_only
  ,sum(case when decreased_borrowers = 1 and increased_borrowers = 0 then 1 else 0 end) decreased_static_borrower_only
  ,sum(case when static_borrowers = 1 and decreased_borrowers = 0 and increased_borrowers = 0 then 1 else 0 end) static_borrower_only
  --Total sum of changes in loan amount and borrowers
  ,sum(total_amount_difference) total_amount_difference
  ,sum(total_borrower_difference) total_borrower_difference
from
(
  --Differences per business (unique name, country, town sector)
  select name, country, town, sector
    ,max(case when amount_difference > 0 then 1 else 0 end) increased_amount
    ,max(case when amount_difference = 0 then 1 else 0 end) static_amount
    ,max(case when amount_difference < 0 then 1 else 0 end) decreased_amount
    ,max(case when borrower_difference > 0 then 1 else 0 end) increased_borrowers
    ,max(case when borrower_difference = 0 then 1 else 0 end) static_borrowers
    ,max(case when borrower_difference < 0 then 1 else 0 end) decreased_borrowers
    ,sum(amount_difference) total_amount_difference
    ,sum(borrower_difference) total_borrower_difference
  from
  (
    --Differences in loan amounts and borrowers
    select name, country, town, sector, loan_id
      ,loan_amount - previous_loan_amount amount_difference
      ,number_of_borrowers - previous_number_of_borrowers borrower_difference
    from
    (
      --Detailed information
      select name, country, town, sector, loan_id, loan_number, number_of_loans
        ,loan_amount
        ,lag(loan_amount) over (partition by name, country, town, sector order by loan_id) previous_loan_amount
        ,number_of_borrowers
        ,lag(number_of_borrowers) over (partition by name, country, town, sector order by loan_id) previous_number_of_borrowers
      from 
      (
        select
          loan.name, loan.country, loan.town, loan.sector, loan.id loan_id
          ,row_number() over (partition by loan.name, loan.country, loan.town, sector order by loan.id) loan_number
          ,count(*) over (partition by loan.name, loan.country, loan.town, sector) number_of_loans
          ,loan.loan_amount, borrowers.total number_of_borrowers
          --,loan.* --debug
        from loan
          inner join (select loan_id, count(*) total from borrower group by loan_id) borrowers
            on loan.id = borrowers.loan_id
        where loan.town is not null
      )
      where number_of_loans > 1
      order by name, country, town, sector, loan_id
    )
  )
  group by name, country, town, sector 
)
;


/*
Businesses with multiple loans        6529

Businesses where loan amount increased  4186
Businesses where loan amount stayed the same 1048
Businesses where loan amount decreased  2140
Businesses where loan amount increased or stayed the same (never decreased)  3613
Businesses where loan amount decreased or stayed the same (never increased)  1567
Businesses where loan amount only stayed the same (never increased or decreased)  776

Businesses where number of borrowers increased  148
Businesses where number of borrowers stayed the same 6260
Businesses where number of borrowers decreased  206
Businesses where number of borrowers increased or stayed the same (never decreased) 103
Businesses where number of borrowers decreased or stayed the same (never increased) 161
Businesses where number of borrowers only stayed the same (never increased or decreased) 6220

Sum of all loan amount differences  599650
Sum of all number of borrower differences  -151

*/












------------------------------------------------------------------------------------------------------------------------
--Random anonymous lending statistics using data from 22-DEC-2009 (before I remove the data)
------------------------------------------------------------------------------------------------------------------------

--Total: 3,127,938    Anonymous: 228,633
select count(*) total, sum(case when loan_lender.lender_id = 'Anonymous' then 1 else 0 end) anonymousLending
from loan_lender;



















------------------------------------------------------------------------------------------------------------------------
--Exceptions
--All of these queries should return no rows, else there is a problem
------------------------------------------------------------------------------------------------------------------------

--Any countries that don't match the country table?
select * from loan where country not in (select name from country);

--Borrowers - are there any loans without a single borrower? 
select * from loan where not exists (select * from borrower where loan_id = loan.id);

--Make sure that all language codes are found in the ISO 639.2 table:
select * from loanLanguage where code is null or code not in (select code from loanLanguage);

--EXCEPTIONS: Currencies: Are there any loan.disbursal_currency not in currency.code?
select distinct disbursal_currency from loan where not exists (select * from currency where code = disbursal_currency);


